/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.uca.sisbec.helpers.becario;

import com.uca.sisbec.beans.MateriaXBecarioBean;
import com.uca.sisbec.forms.becario.RegistroMateriaForm;
import com.uca.sisbec.mapping.*;
import java.math.BigDecimal;
import java.util.*;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Expression;

/**
 *
 * @author Susy
 */
public class RegistroMateriasHelper {

    private Session session;
    private Becario becario;
    
    public RegistroMateriasHelper(Session session) {
        this.session = session;
    }
    
    public List<MateriaXBecarioBean> buscarMateriasBecario(RegistroMateriaForm mf,Becario bec){
        System.out.println("--buscarMateriasBecario--");
        List<MateriaXBecarioBean> mxb;
        String hql = "";
        String hql2 = "Select new com.uca.sisbec.beans.MateriaXBecarioBean(concat(mxb.id.carnet,'|',mxb.id.codMateria,'|',mxb.id.correlMxb),m.nombre,mxb.ciclo,mxb.annio,mxb.matricula,mxb.tutoria,mxb.retirada,mxb.notaFinal,mxb.id.codMateria) From MateriaXBecario mxb, Materia m, MateriaXCarrera mxc where mxb.id.carnet = :carnet and m.codMateria=mxb.id.codMateria and mxc.id.codMateria = m.codMateria and mxc.id.codCarrera = :codigo and mxb.ciclo = :cic and mxb.annio = :an";
        
        hql = "From MateriaXBecario mxb,MateriaXCarrera mxc where mxb.id.carnet = :carnet and mxb.ciclo = :cic and mxb.annio = :an"
                + " and mcx.";
        Query query = session.createQuery(hql2);
        query.setString("carnet",bec.getCarnet());
        query.setString("cic", mf.getCiclo());
        query.setString("an",mf.getAnnio());
        query.setInteger("codigo", bec.getCodCarrera());
        mxb = new ArrayList<MateriaXBecarioBean>(query.list());
        System.out.println("El numero de materias es: "+mxb.size());
        return mxb;
    }
    
    public void guardarMateriasXBecario(List<MateriaXBecarioBean> bean,Becario bec){
        MateriaXBecario mxb=null;
        MateriaXBecarioId id = null;
        MateriaXBecarioBean mxbBean;
        for(int i =0; i<bean.size();i++){
            mxbBean = bean.get(i);
            id = new MateriaXBecarioId();
            System.out.println(bec.getCarnet());
            id.setCarnet(bec.getCarnet());
            id.setCodMateria(mxbBean.getCodMateria());
            mxb = new MateriaXBecario();
            mxb.setId(id);
            mxb.setAnnio(mxbBean.getAnnio());
            mxb.setBecario(bec);
            mxb.setCiclo(mxbBean.getCiclo());
            mxb.setMateria((Materia) session.get(Materia.class,mxbBean.getCodMateria()));
            mxb.setMatricula(mxbBean.getMatricula());
            mxb.setNotaFinal(mxbBean.getNotaFinal());
            mxb.setRetirada(mxbBean.getRetirada());
            mxb.setTutoria(mxbBean.getTutoria());
            session.saveOrUpdate(mxb);
        }
    }
    
    public boolean existeMateriaXBecario(MateriaXBecarioBean bean,Becario bec){
        String hql="From MateriaXBecario mxb where mxb.id.codMateria = :codigo and mxb.id.carnet = :carnet and mxb.ciclo = :ciclo"
                + " and mxb.annio= :annio";
        Query query = session.createQuery(hql);
        query.setString("carnet",bec.getCarnet());
        query.setString("codigo", bean.getCodMateria());
        query.setString("ciclo",bean.getCiclo());
        query.setString("annio",bean.getAnnio());
        if(query.list().size()>0){
            return true;
        }else{
            return false;
        }
    }
    
     public MateriaXBecarioBean obtenerObjMateriaXBecario(RegistroMateriaForm mf,Becario bec){
        MateriaXBecarioBean bean = new MateriaXBecarioBean();
        Materia m = (Materia) session.get(Materia.class, mf.getCodMateria());
        bean.setIdCompleto(bec.getCarnet()+"|"+mf.getCodMateria()+"|0");
        bean.setCodMateria(m.getCodMateria());
        bean.setCodCarrera(mf.getCodCarrera());
        bean.setAnnio(mf.getAnnio());
        bean.setCiclo(mf.getCiclo());
        bean.setMatricula(mf.getMatricula());
        bean.setNombreMateria(m.getNombre());
        bean.setNotaFinal(BigDecimal.valueOf(mf.getNota()));
        bean.setRetirada(mf.getRetirada()?"S":"N");
        bean.setTutoria(mf.getTutoria()?"S":"N");
        
       return bean;
    }
    
    public List<Materia> obtenerMateriasXCarrera(RegistroMateriaForm mf,Becario bec){
        List<Materia> materias;
        List<MateriaXCarrera> mxc;
        String hql2="Select materia From MateriaXCarrera as mxc where mxc.id.codCarrera = :carrera "
                + "and mxc.id.codMateria not in (select mxb.id.codMateria from MateriaXBecario mxb,MateriaXCarrera cxm where mxb.id.carnet= :carnet and mxb.annio= :annio and mxb.ciclo= :ciclo and cxm.id.codCarrera= :carrera and cxm.id.codMateria=mxb.id.codMateria)";
        Query query = session.createQuery(hql2);
        int codCarr = bec.getCodCarrera();
        query.setInteger("carrera",codCarr);
        query.setString("carnet",bec.getCarnet());
        query.setString("annio",mf.getAnnio());
        query.setString("ciclo", mf.getCiclo());
        materias = new ArrayList<Materia>(query.list());
        return materias;
        
        
        /*
        List<Materia> materias;
        String hql = "From Materia";
        Query query = session.createQuery(hql);
        materias = new ArrayList<Materia>(query.list());
        return materias;
        /
        
        /*Becario bec = (Becario) buscarBecarioDB("00008206");
        int codCarr = bec.getCodCarrera();
        int idU = bec.getUniversidad().getIdUniv();
        CarreraId cId = new CarreraId(codCarr, idU);
        Carrera car = (Carrera) session.get(Carrera.class, cId);
        session.update(car);
        List<MateriaXCarrera> lista = new ArrayList<MateriaXCarrera>(car.getMateriaXCarreras());
        return lista;*/
        
    }
    
     public Becario buscarBecarioDB(String carnet) {
        Becario bec;
        Query queryResult = session.createQuery("From Becario where carnet = :carnet");
        queryResult.setParameter("carnet", carnet);
        bec = (Becario) queryResult.uniqueResult();
        System.out.println("El carnet del becario es: " + bec.getCarnet());
        return bec;

    }
     
    public Carrera obtenerCarrera(String carnet){
        System.out.println("Entre a obtener Carrera");
        CarreraXBecario car;
        Query queryResult = session.createQuery("From CarreraXBecario where id.carnet = :carnet and estado='A' ");
        queryResult.setParameter("carnet", carnet);
        car = (CarreraXBecario) queryResult.uniqueResult();
        System.out.println("La carrera es: "+car.getCarrera().getCarrera());
        return car.getCarrera();
    }
    
    public List<Carrera> getAllCarreras(RegistroMateriaForm mf){
        List<Carrera> allCarreras;
        String hql = "";
        hql = (mf.getIdUniv() != 0) ? "From Carrera where id.idUniv = :univ" : "From Carrera";
        Query query = session.createQuery(hql);
        if (mf.getIdUniv() != 0)
            query.setInteger("univ", mf.getIdUniv());
        allCarreras = new ArrayList<Carrera>(query.list());
        return allCarreras;
    }
    
    public List<Materia> getAllMaterias(RegistroMateriaForm mf){
        List<Materia> allMaterias;
        String hql = "";
        hql = (mf.getCodCarrera() != 0) ?"Select materia From MateriaXCarrera as mxc where mxc.id.codCarrera = :carrera and mxc.materia.estado='A'":"Select materia From MateriaXCarrera as mxc where mxc.materia.estado='A'";
        Query query = session.createQuery(hql);
        if(mf.getCodCarrera() !=0)
            query.setInteger("carrera", mf.getCodCarrera());
        allMaterias = new ArrayList<Materia>(query.list());
        return allMaterias;
    }
    
     public List<Materia> buscarMateriaDB(RegistroMateriaForm mf) {
        System.out.println("Entre a buscar Materia");
        Criteria crit = session.createCriteria(Materia.class).add(Expression.conjunction().add(Expression.like("nombre", "%" + mf.getNombre() + "%")));
        List<Materia> listMat = new ArrayList<Materia>(crit.list());
        System.out.println("El tamaño de la lista de  becarios es: " + listMat.size());
        return listMat;
    }
    
    
    public List<Universidad> getUniversidadesActivas(){
        List<Universidad> allUniversidades;
        Query queryResult = session.createQuery("From Universidad where estado='A'");
        allUniversidades = new ArrayList<Universidad>(queryResult.list());
        return allUniversidades;
    }
    
    public boolean existeCodMateria(RegistroMateriaForm mf){
       boolean existe = false;
       Materia m = (Materia) session.get(Materia.class, mf.getCodMateria());
       if(m!=null) existe=true;
       return existe;
    }
    
    public void agregarMateria(RegistroMateriaForm mf){
        Materia mat = new Materia();
        mat.setCodMateria(mf.getCodMateria());
        mat.setNombre(mf.getNombre());
        mat.setUv(mf.getUv());
        mat.setEstado(mf.isEstado()?"A":"I");
        
        MateriaXCarreraId mxcId = new MateriaXCarreraId(mf.getCodMateria(),mf.getCodCarrera(),mf.getIdUniv());
        MateriaXCarrera mxc = new MateriaXCarrera();
        mxc.setId(mxcId);
        mxc.setMateria(mat);
        CarreraId cId = new CarreraId(mf.getCodCarrera(),mf.getIdUniv());
        mxc.setCarrera((Carrera) session.get(Carrera.class, cId));
        
        session.save(mat);
        session.save(mxc);
        
    }

    public void InhabilitarMateriaDB(String codMateria) {
        Materia m = (Materia) session.get(Materia.class,codMateria);
        m.setEstado("I");
        session.update(m);
    }

    public void HabilitarMateriaDB(String codMateria) {
        Materia m = (Materia) session.get(Materia.class,codMateria);
        m.setEstado("A");
        session.update(m);
    }
    
    public void EliminarMatXBecDB(String carnet,String codMat, int correl){
        System.out.println("entre a Eliminar MXB");
        MateriaXBecarioId mxbId = new MateriaXBecarioId(correl,codMat, carnet);
        MateriaXBecario mxb = (MateriaXBecario) session.get(MateriaXBecario.class, mxbId);
        session.delete(mxb);
    }

    public void deleteMaterias(RegistroMateriaForm mf,Becario b) {
        String hql="select mxb from MateriaXBecario mxb,MateriaXCarrera cxm where mxb.id.carnet= :carnet and mxb.annio= :annio and mxb.ciclo= :ciclo and cxm.id.codCarrera= :carrera and cxm.id.codMateria=mxb.id.codMateria";
        Query query = session.createQuery(hql);
        query.setString("carnet", b.getCarnet());
        query.setString("annio", mf.getAnnio());
        query.setString("ciclo", mf.getCiclo());
        query.setInteger("carrera",b.getCodCarrera());
        List matXbec = query.list();
        MateriaXBecario mxb = null;
        for(Iterator iter=matXbec.iterator(); iter.hasNext();){
            mxb=(MateriaXBecario) iter.next();
            session.delete(mxb);
        }
    }
}
